package com.lxk.mapper;

import com.lxk.bean.Constants;
import com.lxk.bean.FieldInfo;
import com.lxk.bean.TableInfo;
import com.lxk.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TableInfoMapper {
    private static final Logger logger = LoggerFactory.getLogger(TableInfoMapper.class);

    private static final String SQL_SHOW_TABLES_STATUS = "show table status";
    private static final String SQL_SHOW_TABLES_FIELDS = "show full fields from %s";
    private static final String SQL_SHOW_TABLES_INDEX = "show index from %s";

    /**
     * 获取表信息
     */
    public List<TableInfo> getTables(Connection conn) {
        List<TableInfo> tableInfoList = new ArrayList<>();

        try (PreparedStatement ps = conn.prepareStatement(SQL_SHOW_TABLES_STATUS);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                String tableName = rs.getString("name");
                String comment = rs.getString("comment");

                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(tableName.indexOf("_") + 1);
                }
                beanName = processField(beanName, true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_Query);

                // 获取表字段信息
                readFieldInfo(conn, tableInfo);

                // 获取索引信息
                getKeyIndexInfo(conn, tableInfo);

                tableInfoList.add(tableInfo);
            }
        } catch (Exception e) {
            logger.error("获取数据库表失败", e);
        }

        return tableInfoList;
    }

    /**
     * 获取表字段信息
     */
    private static void readFieldInfo(Connection conn, TableInfo tableInfo) {
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        List<FieldInfo> fieldExtendList = new ArrayList<>();

        try (PreparedStatement ps = conn.prepareStatement(String.format(SQL_SHOW_TABLES_FIELDS, tableInfo.getTableName()));
             ResultSet fieldrs = ps.executeQuery()) {

            boolean haveDateTime = false;
            boolean haveDate = false;
            boolean haveBigDecimal = false;

            while (fieldrs.next()) {
                String field = fieldrs.getString("field");
                String type = fieldrs.getString("type");
                String extra = fieldrs.getString("extra");
                String comment = fieldrs.getString("comment");

                String propertyName = processField(field, false);
                if (type.contains("(")) {
                    type = type.substring(0, type.indexOf("("));
                }

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);

                fieldInfo.setFieldName(field);
                fieldInfo.setComment(comment);
                fieldInfo.setSqlType(type);
                fieldInfo.setIsAutoIncrement(!"".equalsIgnoreCase(extra));
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));

                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)) {
                    haveDateTime = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
                    haveDate = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, type)) {
                    haveBigDecimal = true;
                }

                // 扩展属性
                if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, type)) {
                    FieldInfo fuzzyField = new FieldInfo();
                    fuzzyField.setJavaType(fieldInfo.getJavaType());
                    fuzzyField.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    fuzzyField.setFieldName(fieldInfo.getFieldName());
                    fuzzyField.setSqlType(type);
                    fieldExtendList.add(fuzzyField);
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type) || ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
                    FieldInfo timeStartField = new FieldInfo();
                    timeStartField.setJavaType("String");
                    timeStartField.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_START);
                    timeStartField.setFieldName(fieldInfo.getFieldName());
                    timeStartField.setSqlType(type);
                    fieldExtendList.add(timeStartField);

                    FieldInfo timeEndField = new FieldInfo();
                    timeEndField.setJavaType("String");
                    timeEndField.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_END);
                    timeEndField.setFieldName(fieldInfo.getFieldName());
                    timeEndField.setSqlType(type);
                    fieldExtendList.add(timeEndField);
                }
            }
            tableInfo.setHavaDateTime(haveDateTime);
            tableInfo.setHavaDate(haveDate);
            tableInfo.setHavaBigDecimal(haveBigDecimal);
            tableInfo.setFieldList(fieldInfoList);
            tableInfo.setFieldExtendList(fieldExtendList);

        } catch (Exception e) {
            logger.error("读取表失败", e);
        }
    }

    /**
     * 获取表索引信息
     */
    private static void getKeyIndexInfo(Connection conn, TableInfo tableInfo) {
        try (PreparedStatement ps = conn.prepareStatement(String.format(SQL_SHOW_TABLES_INDEX, tableInfo.getTableName()));
             ResultSet fieldrs = ps.executeQuery()) {

            Map<String, FieldInfo> tempMap = new HashMap<>();
            for (FieldInfo fieldInfo : tableInfo.getFieldList()) {
                tempMap.put(fieldInfo.getFieldName(), fieldInfo);
            }

            while (fieldrs.next()) {
                String keyName = fieldrs.getString("key_name");
                Integer nonUnique = fieldrs.getInt("non_unique");
                String columnName = fieldrs.getString("column_name");

                if (nonUnique == 1) {
                    // 非唯一索引
                    continue;
                }

                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if (keyFieldList == null) {
                    keyFieldList = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName, keyFieldList);
                }
                keyFieldList.add(tempMap.get(columnName));
            }
        } catch (Exception e) {
            logger.error("读取索引失败", e);
        }
    }

    /**
     * 字段转驼峰
     */
    private static String processField(String field, Boolean uperCaseFirstLetter) {
        StringBuilder sb = new StringBuilder();
        String[] fields = field.split("_");
        sb.append(uperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1; i < fields.length; i++) {
            sb.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }

    /**
     * SQL 类型转 Java 类型
     */
    private static String processJavaType(String type) {
        if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPES, type)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPES, type)) {
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, type)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type) || ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)) {
            return "Date";
        } else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, type)) {
            return "BigDecimal";
        } else {
            throw new RuntimeException("无法识别的类型：" + type);
        }
    }
}
